import os
import re
from io import IncrementalNewlineDecoder
from typing import Sequence, Set

import pandas as pd
import xlwings as xw
from pandas.core import base
from xlwings.main import Books

app = xw.App(visible=False, add_book=False)
发货人集 = dict()


class eachfile():
    # 1.创建或打开汇总文件
    # 2.子过程-遍历文件
    # 1.
    # n.关闭汇总文件
    def __init__(self):
        self.app = xw.App(visible=False, add_book=False)
        self.app.screen_updating = False
        global 发货人集
        发货人集['发货人'] = dict()
        发货人集['成交计量单位'] = dict()
        发货人集['币制'] = dict()
        发货人集['境内目的地/境内货源地'] = dict()
        发货人集['商品编码'] = dict()
        发货人集['目的国'] = dict()
        self.参数集 = dict()
        self.初始化参数集()
        self.code_item = self.code_items()

    def go解析(self, base=r'.\工作区文件夹'):
        for f in self.findAllFile(base=base):
            XY = self.READ_TABLE(file=f)
            if '' in XY:
                continue
            # if pd.read_excel(io=open(f, 'rb'), sheet_name='基本信息&商品信息', header=21, dtype='str').empty:
                # continue
            self.argset(XY=XY)
            self.READ_ITEM(file=f)
        # print(发货人集)
        self.保存到表格()

    def 保存到表格(self):
        # 保存申报要素汇总表
        # bk=self.app.books.open(r'.\申报要素汇总表.xlsx') if os.path.exists(r'.\申报要素汇总表.xlsx') else self.app.books.add()
        try:
            bk = self.app.books.open(r'.\申报要素汇总表.xlsx')
        except FileNotFoundError:
            bk = self.app.books.add()
            bk.save(r'.\申报要素汇总表.xlsx')
        sheet_names = [j.name for j in bk.sheets]
        for company, icode in sorted(发货人集['发货人']):
            print(company)
            if not(company in sheet_names):
                bk.sheets.add(company)
            sht = bk.sheets[company]
            sht.clear()
            sht['a1:c{}'.format(发货人集['发货人'][(company, icode)]
                                ['申报要素汇总']['商品编号'].count()+2)].number_format = '@'
            sht.range('a1').value = '统一代码:'
            sht.range('b1').value = icode
            sht.range('a2').value = 发货人集['发货人'][(
                company, icode)]['申报要素汇总'].drop('对比项', axis=1).set_index('申报日期')
            # sht.range('a2').options(index=False).value = 发货人集['发货人'][(
            #     company, icode)]['申报要素汇总'].drop('对比项', axis=1).set_index('申报日期')
            sht.range('A1').expand('table').autofit()
        # bk.save(f'申报要素汇总表')
        bk.save()
        bk.close()

        # 参数汇总表
        try:
            bk = self.app.books.open(r'.\参数汇总表.xlsx')
        except FileNotFoundError:
            bk = self.app.books.add()
            bk.save(r'.\参数汇总表.xlsx')
        sheet_names = [j.name for j in bk.sheets]
        for dict_name in 发货人集:
            print(dict_name)
            if dict_name in ['成交计量单位', '币制', '境内目的地/境内货源地', '目的国']:
                temp_ites = sorted(
                    发货人集[dict_name].items(), key=lambda x: x[1], reverse=True)
                if dict_name == '境内目的地/境内货源地':
                    dict_name = '境内目的地&境内货源地'
                if not(dict_name in sheet_names):
                    bk.sheets.add(dict_name)
                sht = bk.sheets[dict_name]
                sht.clear()
                sht['a1:c%s' % (len(temp_ites)+1)].number_format = '@'
                sht.range('a1').value = [dict_name, '代码', '计次器']
                for i, set_item in enumerate(temp_ites):
                    aaa = list(set_item[0])
                    aaa.append(set_item[1])
                    sht.range('a{}'.format(str(i+2))).value = aaa
            # //todo保存编码计量单位关系
            if dict_name in ['商品编码']:
                if not(dict_name in sheet_names):
                    bk.sheets.add(dict_name)
                sht = bk.sheets[dict_name]
                sht.clear()
                sht['a1:e%s' % (len(发货人集[dict_name])+1)].number_format = '@'
                sht.range('a1').value = [dict_name, '第一法定', '代码', '第二法定', '代码']
                i = 1
                for set_item in sorted(发货人集[dict_name]):
                    i += 1
                    sht.range('a{}'.format(i)).value = [
                        set_item, *发货人集[dict_name][set_item].split(',')]

        bk.save()
        bk.close()

    def 初始化参数集(self, file=r'.\参数表\参数汇总.xlsx'):
        bk = self.app.books.open(file)
        sheet_names = [j.name for j in bk.sheets]
        bk.close()
        a = ['货币代码表', '计量单位代码表',  '国别（地区）代码表', '国内地区代码表']
        b = ['币制', '成交计量单位', '启运国/抵运国', '境内目的地/境内货源地']
        临时 = pd.read_excel(io=open(file, 'rb'),
                           sheet_name=sheet_names, dtype="str")
        for k, v in zip(a, b):
            self.参数集[v] = {}
            for i in 临时[k].values:
                self.参数集[v]['%s' % i[0]] = '%s' % i[1]

    def READ_ITEM(self, file='样版.xlsx'):
        '''
        //note:返回字典含商品项各申报要素
        '''
        temp_item = pd.read_excel(
            io=open(file, 'rb'), sheet_name='基本信息&商品信息', header=21, dtype='str')
        if temp_item.empty:
            return
        temp_item.fillna(value='nan', inplace=True)
        for i in temp_item:
            if not(i in ('成交计量单位', '币制', '境内目的地/境内货源地', '商品编号')):
                continue
            item = None
            if i in ('成交计量单位', '币制', '境内目的地/境内货源地'):

                item = temp_item[i].drop_duplicates()
                for v in item.values:
                    if v == 'nan':
                        continue
                    发货人集[item.name][(self.参数集[item.name][v], v)] = 发货人集[item.name][(self.参数集[item.name][v], v)] + \
                        1 if 发货人集[item.name].get(
                            (self.参数集[item.name][v], v)) else 1

            if i == '商品编号':
                # for i1, i2, i3 in temp_item[['商品编号', '法定第一计量单位', '法定第二计量单位']].values:
                for i1, i2, i3 in zip(temp_item['商品编号'], temp_item['法定第一计量单位'], temp_item['法定第二计量单位']):
                    if i3 == 'nan':
                        # if isinstance(i3, float):
                        发货人集['商品编码'][i1] = '%s,%s' % (
                            self.参数集['成交计量单位'][i2], i2)
                    else:
                        发货人集['商品编码'][i1] = '%s,%s,%s,%s' % (
                            self.参数集['成交计量单位'][i2], i2, self.参数集['成交计量单位'][i3], i3)

        # bk = self.app.books.open(file)
        # sht = bk.sheets['基本信息&商品信息']
        # CELL = [sht.range(i).value for i in ('F3', 'D3')]
        # bk.close()
<<<<<<< HEAD
        CELL = self.READ_TABLE(file=file, XY=['F3', 'D3','B3'],)
=======
        CELL = self.READ_TABLE(file=file, XY=['F3', 'D3','b3'])
>>>>>>> 82439bd6e35dc1a90cabe8496ed75e79dfc60c64
        发货公司 = ('%s' % CELL[0], '%s' % CELL[1])
        temp_item = temp_item[['商品编号', '商品名称', '规格型号']]
        temp_item['申报日期']=pd.to_datetime(CELL[2])
        temp_item['对比项'] = temp_item.apply(
            self.分离申报要素, args=(self.code_item,), axis=1)
        if 发货人集['发货人'][发货公司]['申报要素汇总'] is None:
            发货人集['发货人'][发货公司]['申报要素汇总'] = temp_item.drop_duplicates(
                subset=['商品编号', '商品名称', '对比项'], keep='last').sort_values(by=['商品编号', '商品名称']).reset_index(drop=True)
        else:
            发货人集['发货人'][发货公司]['申报要素汇总'] = pd.concat(
                [发货人集['发货人'][发货公司]['申报要素汇总'], temp_item], ignore_index=True).sort_values(by=['申报日期']).drop_duplicates(
                subset=['商品编号', '商品名称', '对比项'], keep='last').sort_values(by=['商品编号', '商品名称']).reset_index(drop=True)

    # //todo:未完成↓
    def READ_TABLE(self, file='样版.xlsx', XY=['F3', 'D3', 'D4', 'J2', 'B3', 'D8']):
        '''
        //NOTE返回表头项
        默认return\n
        0 F3发货人名称\n
        1 D3发货人代码\n
        2 D4收货人名称\n
        3 J2合同号\n
        4 B3申报日期\n
        5 D8目的国
        '''
        bk = self.app.books.open(file)
        sht = bk.sheets['基本信息&商品信息']
        CELL = [sht.range(i).value for i in XY]
        bk.close()
        return CELL

    def argset(self, XY=['发货人名称', '发货人代码', '收货人名称', '合同号', '出口日期', '目的国']):
        '''
        XY[0]=发货人名称\n XY[1]=发货人代码\n XY[2]=收货人名称
        '''
        发货公司 = ('%s' % XY[0], '%s' % XY[1])

        # 目的国
        发货人集['目的国'][(self.参数集['启运国/抵运国'][XY[5]], XY[5])] = 发货人集['目的国'][(self.参数集['启运国/抵运国'][XY[5]], XY[5])] + \
            1 if (self.参数集['启运国/抵运国'][XY[5]], XY[5]) in 发货人集['目的国'] else 1

        # 创建发货公司结构
        if not(发货公司 in 发货人集['发货人']):
            发货人集['发货人'][发货公司] = dict()
            发货人集['发货人'][发货公司]['收货人集'] = dict()
            发货人集['发货人'][发货公司]['申报要素汇总'] = None  # DataFrame

        # 创建收货人结构//todo XY[2]->xy2==收货人
        xy2 = ' '.join(re.split('\s+', XY[2], flags=re.UNICODE)).strip(' .。')
        if not(xy2 in 发货人集['发货人'][发货公司]['收货人集']):
            发货人集['发货人'][发货公司]['收货人集'][xy2] = dict()
            发货人集['发货人'][发货公司]['收货人集'][xy2]['计次器'] = 1
            发货人集['发货人'][发货公司]['收货人集'][xy2]['合同号'] = dict()
        else:
            发货人集['发货人'][发货公司]['收货人集'][xy2]['计次器'] += 1

        if not(XY[3] in 发货人集['发货人'][发货公司]['收货人集'][xy2]['合同号']):
            发货人集['发货人'][发货公司]['收货人集'][xy2]['合同号'][XY[3]] = {'%s' % XY[4], }
        else:
            发货人集['发货人'][发货公司]['收货人集'][xy2]['合同号'][XY[3]].add(XY[4])

        return 发货人集
    # //TODO###未完成↑

    ###//TODO###解析申报要素并去重###
    def findAllFile(self, base='.\工作区文件夹'):
        for root, ds, fs in os.walk(base):
            for f in fs:
                if f.split(".")[1] in ['xls', 'xlsx'] and not(f.startswith('~$')):
                    fullname = os.path.join(root, f)
                    print(fullname)
                    yield fullname

    def 商品项(self, base='.\工作区文件夹'):
        '''

        '''
        df = None
        for file in self.findAllFile(base):

            _df = pd.read_excel(io=open(file, 'rb'), sheet_name='基本信息&商品信息', header=21, dtype=str)[
                ['商品编号', '规格型号']]
            # _df['商品编号'] = _df['商品编号'].astype(str)
            if df is None:
                df = _df
            else:
                df = pd.concat([df, _df], ignore_index=True).drop_duplicates(subset='商品编号',
                                                                             keep='last').sort_values('商品编号')
        # print(df)
        wb = self.app.books.add()
        sht = wb.sheets[0]
        sht['a1:a{}'.format(df['商品编号'].count()+1)].number_format = '@'
        df['对比项'] = df.apply(self.分离申报要素, args=(self.code_item,), axis=1)
        sht.range('A1').options(index=False).value = df
        sht.range('A1').expand('table').autofit()
        wb.save(r'.\argfile.xlsx')
        wb.close()

    def 分离申报要素(self, df, code_item):
        # return '|'.join(df['规格型号'].split('|')[2:code_item[df['商品编号']]])
        
        # return '|'.join(pd.Series(df['规格型号'].split('|'))[code_item[df['商品编号']]].to_list())
        strlist=list(pd.Series(df['规格型号'].split('|'))[code_item[df['商品编号']]].fillna(value='nan'))
        return '|'.join(strlist)
        

    # //todo修改210325

    def code_items(self, path=r'.\argfile3.xlsx'):
        """ c_item字典存入数值列表 """
        c_item = {}
        bk = self.app.books.open(path)
        sht = bk.sheets[0]
        for k, v, i in sht.range('A2').expand('table').value:
            s_list = v.split(',')
            if len(s_list) > 1:
                a = set(range(2, int(s_list[0])))
                b = set(list(map(lambda x: int(x)-1, s_list[1:])))
                c_item[k] = list(a-b)

            else:
                c_item[k] = list(range(2, int(v)))
        bk.close()
        return c_item

    ###//TODO###解析申报要素并去重###end###


if __name__ == '__main__':
    import time
    start = time.time()


    aaa = eachfile()

    # aaa.go解析(base=r'.\工作区文件夹\1e32b4d7-953f-43c6-a6e2-aec17e883d28')
    # aaa.go解析(base=r'.\工作区文件夹\923f7b84-9c6d-48b4-ae3b-88323031ac58')
<<<<<<< HEAD
    # aaa.go解析(base=r'.\工作区文件夹\cc56a512-ae3f-484a-b0e1-bb085a613c39')
    # aaa.go解析('.\\工作区文件夹\\61985878-883e-4156-809f-a2ba1372fb28')
    aaa.go解析()
=======
    aaa.go解析(base=r'.\工作区文件夹\cc56a512-ae3f-484a-b0e1-bb085a613c39')
    # aaa.go解析()
>>>>>>> 82439bd6e35dc1a90cabe8496ed75e79dfc60c64
    # aaa.code_items()

    # aaa.初始化参数集()
    # print(aaa.argset(aaa.READ_TABLE()))

    # aaa.READ_ITEM()

    # ac = pd.Series()

    # # aaa.商品项(r'.\工作区文件夹\97fb8e02-14fe-4d08-ab34-3fd2e20e7f52')

    # ab = pd.DataFrame()
    # pass

    # ab.i
    # bbb=pd.DataFrame()
    # bbb.applymap()

    # for i in aaa.items_test():
    #     if isinstance(i, pd.DataFrame):
    #         for i1, i2, i3 in i.values:
    #             发货人集['商品编码'][i1] = (i2, i3)
    #     else:
    #         for i0 in i.values:
    #             发货人集[i.name][i0] = 发货人集[i.name][i0] + \
    #                 1 if 发货人集[i.name].get(i0) else 1

    # aaa.argset(aaa.READ_TABLE())

    # print(发货人集)
    end = time.time()
    print(str((end-start)/60))
